CentOS 7
Sponsored Link

SQL Server 2017 : Use from Python
2017/10/28
 
This is an example to use SQL Server from Python.
[1] Install Python DB API for ODBC first.
# install EPEL

[root@dlp ~]#
yum --enablerepo=epel -y install pyodbc
[2] Create a sample User and Database for Test.
[root@dlp ~]#
sqlcmd -S localhost -U SA

Password:
# create login user
1> create login cent with PASSWORD= N'password';
2> go

# create [SampleDB]
1> create database SampleDB;
2> go

1> use SampleDB;
2> go
Changed database context to 'SampleDB'.

# create DB user
1> create user cent for login cent;
2> go

# asign DB owner role to [cent]
1> exec sp_addrolemember 'db_owner', 'cent';
2> go

# create [SampleTable]
1> create table SampleTable (
2> ID int identity(1,1) not null primary key, First_Name NVARCHAR(50), Last_Name NVARCHAR(50)
3> );
4> insert into SampleTable (
5> First_Name, Last_Name) values (N'CentOS', N'Linux'), (N'RedHat', N'Linux'), (N'Fedora', N'Linux'
6> );
7> go
[3] There are some basic usage to connect to SQL Server from Python.
[cent@dlp ~]$
vi use_mssql.py 
import pyodbc

server = '127.0.0.1'
database = 'SampleDB'
username = 'cent'
password = 'password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server}; \
    SERVER='+server+'; PORT=1443; DATABASE='+database+'; UID='+username+'; PWD='+ password)
cursor = cnxn.cursor()

# Select from SampleTable
print ('\nReading data from SampleTable')
tsql = "select * from SampleTable;"
with cursor.execute(tsql):
    row = cursor.fetchone()
    while row:
        print (str(row[0]) + " " + str(row[1]) + " " + str(row[2]))
        row = cursor.fetchone()

# Insert from SampleTable
print ('\nInserting a new row into SampleTable')
tsql = "insert into SampleTable (First_Name, Last_Name) values (?,?);"
with cursor.execute(tsql,'Ubuntu','Linux'):
    print ('- Successfuly Inserted!')

# Update from SampleTable
print ('\nUpdating Last_Name for Redhat')
tsql = "update SampleTable set Last_Name = ? where First_Name = ?"
with cursor.execute(tsql,'Maipo','Redhat'):
    print ('- Successfuly Updated!')

tsql = "select * from SampleTable;"
with cursor.execute(tsql):
    row = cursor.fetchone()
    while row:
        print (str(row[0]) + " " + str(row[1]) + " " + str(row[2]))
        row = cursor.fetchone()

# Delete from SampleTable
print ('\nDeleting user Ubuntu')
tsql = "delete from SampleTable where First_Name = ?"
with cursor.execute(tsql,'Ubuntu'):
    print ('- Successfuly Deleted!')
[cent@dlp ~]$
python use_mssql.py 


Reading data from SampleTable
1 CentOS Linux
2 RedHat Linux
3 Fedora Linux

Inserting a new row into SampleTable
- Successfuly Inserted!

Updating Last_Name for Redhat
- Successfuly Updated!
1 CentOS Linux
2 RedHat Maipo
3 Fedora Linux
5 Ubuntu Linux

Deleting user Ubuntu
- Successfuly Deleted!
 
Tweet